package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.CellFormat;
import jxl.Workbook;
import jxl.biff.FontRecord;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.apache.commons.net.ntp.TimeStamp;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.jboss.util.propertyeditor.BigDecimalEditor;
import org.joda.time.DateTime;

import com.lowagie.text.Font;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class FI_Form3 extends SvrProcess {

	/** Current context */
	private Properties m_ctx;
	/**     */
	private ProcessInfo pi;

	private String startDate = null;
	private String endDate = null;
	private String startDateMinusYear = null;
	private String endDateMinusYear = null;
	private String getSQlresult = null;
	private Timestamp tsEndDate = null;
	private Timestamp tsStartDate = null;
	private Timestamp tsEndDateMinusYear = null;
	private Timestamp tsStartDateMinusYear = null;	
	private boolean Thousand = false;

	private static CLogger s_log = CLogger.getCLogger(BalanceSheets.class);
	/** */
	private String path = "";

	class line {
		BigDecimal v541000 = null;  BigDecimal v771000 = null;
		BigDecimal v232000 = null;  BigDecimal v242000 = null;	BigDecimal v262002 = null;  BigDecimal v274000 = null;
		BigDecimal v272000 = null;
		BigDecimal v744000 = null;
		BigDecimal v621000 = null; 	BigDecimal v741000 = null;
		BigDecimal v614000 = null;
		BigDecimal v615000 = null;
		BigDecimal v611000 = null;	BigDecimal v612000 = null; 	BigDecimal v613000 = null;	BigDecimal v616000 = null;	BigDecimal v7300 = null;
		BigDecimal v335000 = null;	
		BigDecimal v625000 = null; 	BigDecimal v743000 = null;
		BigDecimal v7600 = null;
		BigDecimal v1300 = null; 	BigDecimal v1340 = null;	BigDecimal v1350 = null;
		BigDecimal v1360 = null; 	BigDecimal v3430 = null;
		BigDecimal v1200 = null;	BigDecimal v1250 = null;	BigDecimal v1280 = null;	BigDecimal v161000 = null;	BigDecimal v163000 = null;	BigDecimal v2100 = null;	BigDecimal v2150 = null;	
			BigDecimal v2180 = null;	BigDecimal v291000 = null;
			
		BigDecimal v331000 = null;	BigDecimal v332000 = null;	BigDecimal v333000 = null;	BigDecimal v334000 = null;	BigDecimal v336000 = null;	BigDecimal v337000 = null;	BigDecimal v338000 = null;	
			BigDecimal v3390 = null;	BigDecimal v341000 = null;	BigDecimal v342000 = null;	BigDecimal v4100 = null;	BigDecimal v4170 = null;	BigDecimal v421000 = null;	BigDecimal v422000 = null;
			
		BigDecimal v3100 = null;	BigDecimal v3200 = null;
		
		BigDecimal v3500 = null;	BigDecimal v441000 = null;	BigDecimal v443000 = null;		
		
		BigDecimal v41 = null;
	}
	
	
	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			 else if (name.equals("startDate"))
				tsStartDate = (Timestamp) para[i].getParameter();
			else if (name.equals("endDate"))
				tsEndDate = (Timestamp) para[i].getParameter();
			 else if (name.equals("Thousand"))
			 Thousand = ((String) para[i].getParameter()).equals("Y");
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

		Date lEndDate = new Date(tsEndDate.getTime());
		SimpleDateFormat format = new SimpleDateFormat("dd.MM.yyyy");
		int year = lEndDate.getYear() + 1900;
		endDate = format.format(lEndDate);
 
		endDateMinusYear = endDate.substring(0, 6) + String.valueOf(year - 1);

		 lEndDate = new Date(tsStartDate.getTime());
		 year = lEndDate.getYear() + 1900;
		 startDate = format.format(lEndDate);
		
		 startDateMinusYear = startDate.substring(0, 6) + String.valueOf(year
		 - 1);

//		startDate = "01.01." + String.valueOf(year);
//		startDateMinusYear = "01.01." + String.valueOf(year - 1);
		// endDateMinusYear = endDate.substring(0, 6)
		// + String.valueOf(year - 1);

		// ---------- get Timestamp dates ---------------{
//		Date d = null;
		Date d2 = null;
		Date d3 = null;
		try {
//			d = format.parse(startDate);
			d2 = format.parse(startDateMinusYear);
			d3 = format.parse(endDateMinusYear);			
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		d.parse(endDate);
//		tsStartDate = new Timestamp(d.getTime());
		tsStartDateMinusYear = new Timestamp(d2.getTime());
		tsEndDateMinusYear = new Timestamp(d3.getTime());		
		//}
		
		
		path = Util.localFilePath;
		// / here another code)
	}

	@Override
	protected String doIt() throws Exception {
		// Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName = "FI_Form3_tmpl.xls";
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,
					"NotFoundTemplate") + " " + templateName);

		File templateCopy = new File(Util.localFilePath + "FI_Form3.xls");
		File temporaryFile = entry.getFile(Util.localFilePath + templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
			return "Error tableBook.";
		}

		String org_name;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sql = new StringBuilder(
				"select name from ad_org where ad_org_id = 1000000");
		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				org_name = rs.getString(1);
				WritableFont arial10ptBold = new WritableFont(
						WritableFont.TIMES, 9, WritableFont.NO_BOLD);
				WritableCellFormat captionStyle = new WritableCellFormat(
						arial10ptBold);
				label = new Label(0, 5,
						("Наименование организации: " + org_name), captionStyle);
				sheet.addCell(label);

			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_Form3", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}

		// ExcelCell cellStart = Utils.getCellStart(tableBook,">>");
		// Style for value column
		WritableFont arial10ptBold = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.NO_BOLD);
		WritableCellFormat valuestyle = null;
		if(Thousand)
			valuestyle = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_INTEGER);
		else
			valuestyle = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_FLOAT); 		
		// WritableCellFormat valuestyle = new WritableCellFormat();
		valuestyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyle.setAlignment(Alignment.RIGHT);
		valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
//		valuestyle.setBackground(Colour.getInternalColour(44));
		valuestyle.setWrap(true);

		WritableFont arial10ptNoBold = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.NO_BOLD);
		WritableCellFormat  valuestyleNoBold = null;
		if(Thousand)
			valuestyleNoBold = new WritableCellFormat(arial10ptNoBold,NumberFormats.THOUSANDS_INTEGER);
		else
			valuestyleNoBold = new WritableCellFormat(arial10ptNoBold,NumberFormats.THOUSANDS_FLOAT);		

		valuestyleNoBold.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleNoBold.setAlignment(Alignment.RIGHT);
		valuestyleNoBold.setVerticalAlignment(VerticalAlignment.CENTRE);
		valuestyleNoBold.setBackground(Colour.getInternalColour(31));		
		valuestyleNoBold.setWrap(true);

		WritableFont arial11ptBold = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.BOLD);
		WritableCellFormat  valuestyleBold = null;
		if(Thousand)
			valuestyleBold = new WritableCellFormat(arial11ptBold,NumberFormats.THOUSANDS_INTEGER);
		else
			valuestyleBold = new WritableCellFormat(arial11ptBold,NumberFormats.THOUSANDS_FLOAT);		

		valuestyleBold.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleBold.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleBold.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleBold.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		valuestyleBold.setAlignment(Alignment.RIGHT);
		valuestyleBold.setVerticalAlignment(VerticalAlignment.CENTRE);
		valuestyleBold.setBackground(Colour.getInternalColour(31));		
		valuestyleBold.setWrap(true);
//		
//		get need color
//		for(int i=1; i<100;i++){
//			WritableCellFormat valuestyle22 = new WritableCellFormat(arial10ptBold,NumberFormats.THOUSANDS_INTEGER);;
//			valuestyle22.setBackground(Colour.getInternalColour(i));
//			label = new Label(10, i, Integer.valueOf(i).toString(), valuestyle22);
//			sheet.addCell(label);
//		}
		
		if(!Thousand){
			WritableCellFormat valuestyle3 = new WritableCellFormat();
			label = new Label(2, 9, "", valuestyle3);
			sheet.addCell(label);
			label = new Label(3, 9, "", valuestyle3);
			sheet.addCell(label);

		}
		
		String caption = " за период, заканчивающийся "
				+ endDate.substring(0, 2)
				+ " "
				+ org.utils.DataUtils.getMonthNameRU(
						new Date(tsEndDate.getTime())).toLowerCase() + " "
				+ endDate.substring(6, 10) + " года.";

		WritableFont arial10ptBold2 = new WritableFont(WritableFont.TIMES, 11,
				WritableFont.BOLD);
		WritableCellFormat captionStyle = new WritableCellFormat(arial10ptBold2);
		captionStyle.setAlignment(Alignment.CENTRE);
		captionStyle.setVerticalAlignment(VerticalAlignment.CENTRE);

		label = new Label(0, 8, caption, captionStyle);
		sheet.addCell(label);

		List<line> l = getDatas(tsStartDate, tsEndDate);
		if (getSQlresult != null)
			return Msg.translate(m_ctx, "FI_Form3: Ошибка запроса: " + getSQlresult);		
		
		if (l.size() == 0)
			return Msg.translate(m_ctx, "По заданому параметру нет данных");

		line li = l.get(0);
		
		
		BigDecimal zero = new BigDecimal(0);
		// output datas ----------------------------------{
//		li.v541000 = (li.v541000 == null) ? 0 : li.v541000;
		li.v541000 = (Thousand && li.v541000.compareTo(zero) != 0) ? (li.v541000 =  li.v541000.divide(new BigDecimal(1000))) : li.v541000;
		li.v771000 = (Thousand && li.v771000.compareTo(zero) != 0) ? (li.v771000 =  li.v771000.divide(new BigDecimal(1000))) : li.v771000;

		li.v232000 = (Thousand && li.v232000.compareTo(zero) != 0) ? (li.v232000 =  li.v232000.divide(new BigDecimal(1000))) : li.v232000;
		li.v242000 = (Thousand && li.v242000.compareTo(zero) != 0) ? (li.v242000 =  li.v242000.divide(new BigDecimal(1000))) : li.v242000;
		li.v262002 = (Thousand && li.v262002.compareTo(zero) != 0) ? (li.v262002 =  li.v262002.divide(new BigDecimal(1000))) : li.v262002;
		li.v274000 = (Thousand && li.v274000.compareTo(zero) != 0) ? (li.v274000 =  li.v274000.divide(new BigDecimal(1000))) : li.v274000;
		li.v272000 = (Thousand && li.v272000.compareTo(zero) != 0) ? (li.v272000 =  li.v272000.divide(new BigDecimal(1000))) : li.v272000;
		
		li.v621000 = (Thousand && li.v621000.compareTo(zero) != 0) ? (li.v621000 =  li.v621000.divide(new BigDecimal(1000))) : li.v621000;
		li.v741000 = (Thousand && li.v741000.compareTo(zero) != 0) ? (li.v741000 =  li.v741000.divide(new BigDecimal(1000))) : li.v741000;
		
		li.v614000 = (Thousand && li.v614000.compareTo(zero) != 0) ? (li.v614000 =  li.v614000.divide(new BigDecimal(1000))) : li.v614000;
		
		li.v615000 = (Thousand && li.v615000.compareTo(zero) != 0) ? (li.v615000 =  li.v615000.divide(new BigDecimal(1000))) : li.v615000;
		
		li.v611000 = (Thousand && li.v611000.compareTo(zero) != 0) ? (li.v611000 =  li.v611000.divide(new BigDecimal(1000))) : li.v611000;
		li.v612000 = (Thousand && li.v612000.compareTo(zero) != 0) ? (li.v612000 =  li.v612000.divide(new BigDecimal(1000))) : li.v612000;
		li.v613000 = (Thousand && li.v613000.compareTo(zero) != 0) ? (li.v613000 =  li.v613000.divide(new BigDecimal(1000))) : li.v613000;		
		li.v616000 = (Thousand && li.v616000.compareTo(zero) != 0) ? (li.v616000 =  li.v616000.divide(new BigDecimal(1000))) : li.v616000;
		li.v7300 = (Thousand && li.v7300.compareTo(zero) != 0) ? (li.v7300 =  li.v7300.divide(new BigDecimal(1000))) : li.v7300;
		
		li.v335000 = (Thousand && li.v335000.compareTo(zero) != 0) ? (li.v335000 =  li.v335000.divide(new BigDecimal(1000))) : li.v335000;	

		li.v625000 = (Thousand && li.v625000.compareTo(zero) != 0) ? (li.v625000 =  li.v625000.divide(new BigDecimal(1000))) : li.v625000;
		li.v743000 = (Thousand && li.v743000.compareTo(zero) != 0) ? (li.v743000 =  li.v743000.divide(new BigDecimal(1000))) : li.v743000;
		
		li.v7600 = (Thousand && li.v7600.compareTo(zero) != 0) ? (li.v7600 =  li.v7600.divide(new BigDecimal(1000))) : li.v7600;
		
		li.v1300 = (Thousand && li.v1300.compareTo(zero) != 0) ? (li.v1300 =  li.v1300.divide(new BigDecimal(1000))) : li.v1300;
		li.v1340 = (Thousand && li.v1340.compareTo(zero) != 0) ? (li.v1340 =  li.v1340.divide(new BigDecimal(1000))) : li.v1340;
		li.v1340 = (Thousand && li.v1340.compareTo(zero) != 0) ? (li.v1340 =  li.v1340.divide(new BigDecimal(1000))) : li.v1340;
		
		li.v1360 = (Thousand && li.v1360.compareTo(zero) != 0) ? (li.v1360 =  li.v1360.divide(new BigDecimal(1000))) : li.v1360;
		li.v3430 = (Thousand && li.v3430.compareTo(zero) != 0) ? (li.v3430 =  li.v3430.divide(new BigDecimal(1000))) : li.v3430;		
		
		li.v1200 = (Thousand && li.v1200.compareTo(zero) != 0) ? (li.v1200 =  li.v1200.divide(new BigDecimal(1000))) : li.v1200;
		li.v1250 = (Thousand && li.v1250.compareTo(zero) != 0) ? (li.v1250 =  li.v1250.divide(new BigDecimal(1000))) : li.v1250;
		li.v1280 = (Thousand && li.v1280.compareTo(zero) != 0) ? (li.v1280 =  li.v1280.divide(new BigDecimal(1000))) : li.v1280;
		li.v161000 = (Thousand && li.v161000.compareTo(zero) != 0) ? (li.v161000 =  li.v161000.divide(new BigDecimal(1000))) : li.v161000;
		li.v163000 = (Thousand && li.v163000.compareTo(zero) != 0) ? (li.v163000 =  li.v163000.divide(new BigDecimal(1000))) : li.v163000;
		li.v2100 = (Thousand && li.v2100.compareTo(zero) != 0) ? (li.v2100 =  li.v2100.divide(new BigDecimal(1000))) : li.v2100;
		li.v2150 = (Thousand && li.v2150.compareTo(zero) != 0) ? (li.v2150 =  li.v2150.divide(new BigDecimal(1000))) : li.v2150;
		li.v2180 = (Thousand && li.v2180.compareTo(zero) != 0) ? (li.v2180 =  li.v2180.divide(new BigDecimal(1000))) : li.v2180;
		li.v2180 = (Thousand && li.v2180.compareTo(zero) != 0) ? (li.v2180 =  li.v2180.divide(new BigDecimal(1000))) : li.v2180;
		
		li.v331000 = (Thousand && li.v331000.compareTo(zero) != 0) ? (li.v331000 =  li.v331000.divide(new BigDecimal(1000))) : li.v331000;
		li.v332000 = (Thousand && li.v332000.compareTo(zero) != 0) ? (li.v332000 =  li.v332000.divide(new BigDecimal(1000))) : li.v332000;
		li.v333000 = (Thousand && li.v333000.compareTo(zero) != 0) ? (li.v333000 =  li.v333000.divide(new BigDecimal(1000))) : li.v333000;
		li.v334000 = (Thousand && li.v334000.compareTo(zero) != 0) ? (li.v334000 =  li.v334000.divide(new BigDecimal(1000))) : li.v334000;
		li.v336000 = (Thousand && li.v336000.compareTo(zero) != 0) ? (li.v336000 =  li.v336000.divide(new BigDecimal(1000))) : li.v336000;
		li.v337000 = (Thousand && li.v337000.compareTo(zero) != 0) ? (li.v337000 =  li.v337000.divide(new BigDecimal(1000))) : li.v337000;
		li.v338000 = (Thousand && li.v338000.compareTo(zero) != 0) ? (li.v338000 =  li.v338000.divide(new BigDecimal(1000))) : li.v338000;
		li.v3390 = (Thousand && li.v3390.compareTo(zero) != 0) ? (li.v3390 =  li.v3390.divide(new BigDecimal(1000))) : li.v3390;
		li.v341000 = (Thousand && li.v341000.compareTo(zero) != 0) ? (li.v341000 =  li.v341000.divide(new BigDecimal(1000))) : li.v341000;
		li.v342000 = (Thousand && li.v342000.compareTo(zero) != 0) ? (li.v342000 =  li.v342000.divide(new BigDecimal(1000))) : li.v342000;
		li.v4100 = (Thousand && li.v4100.compareTo(zero) != 0) ? (li.v4100 =  li.v4100.divide(new BigDecimal(1000))) : li.v4100;
		li.v4170 = (Thousand && li.v4170.compareTo(zero) != 0) ? (li.v4170 =  li.v4170.divide(new BigDecimal(1000))) : li.v4170;
		li.v421000 = (Thousand && li.v421000.compareTo(zero) != 0) ? (li.v421000 =  li.v421000.divide(new BigDecimal(1000))) : li.v421000;
		li.v422000 = (Thousand && li.v422000.compareTo(zero) != 0) ? (li.v422000 =  li.v422000.divide(new BigDecimal(1000))) : li.v422000;
		
		li.v422000 = (Thousand && li.v422000.compareTo(zero) != 0) ? (li.v422000 =  li.v422000.divide(new BigDecimal(1000))) : li.v422000;
		li.v3200 = (Thousand && li.v3200.compareTo(zero) != 0) ? (li.v3200 =  li.v3200.divide(new BigDecimal(1000))) : li.v3200;

		li.v3500 = (Thousand && li.v3500.compareTo(zero) != 0) ? (li.v3500 =  li.v3500.divide(new BigDecimal(1000))) : li.v3500;
		li.v441000 = (Thousand && li.v441000.compareTo(zero) != 0) ? (li.v441000 =  li.v441000.divide(new BigDecimal(1000))) : li.v441000;
		li.v443000 = (Thousand && li.v443000.compareTo(zero) != 0) ? (li.v443000 =  li.v443000.divide(new BigDecimal(1000))) : li.v443000;
		
		BigDecimal tmp = null;
		//010 = 541000 минус 771000
		tmp = li.v541000.add(li.v771000);
		number = new Number(2, 12, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//011 = 232000,242000,2620002,274000
		tmp = li.v232000.add(li.v242000).add(li.v262002).add(li.v274000);
		number = new Number(2, 13, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//12 = 272000
		tmp = li.v272000;
		number = new Number(2, 14, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//13 = 744 000
		tmp = li.v744000;
		number = new Number(2, 15, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//15 = 621000 - 741000
		tmp = li.v621000.subtract(li.v741000);
		number = new Number(2, 17, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//16 = 614 000
		tmp = li.v614000;
		number = new Number(2, 18, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//18 = 615 000
		tmp = li.v615000;
		number = new Number(2, 20, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//19 = 611000,612000,613000,615000,616000 минус 7300
		tmp = li.v611000.add(li.v612000).add(li.v613000).add(li.v615000).add(li.v616000).subtract(li.v7300);
		if(li.v7300.equals(new BigDecimal(0)))
			tmp = tmp.multiply(new BigDecimal(-1));
		number = new Number(2, 21, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//20 = 335 000
		tmp = li.v335000;
		number = new Number(2, 22, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//23 = 625000 минус 743000
		tmp = li.v625000.subtract(li.v743000);
		number = new Number(2, 25, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//24 = 6400 минус 7600
		tmp = li.v614000.subtract(li.v7600);
		number = new Number(2, 26, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);			

		tmp = new BigDecimal(0);
		number = new Number(2, 27, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);				
		
		//31 = 1300,1340,1350
		tmp = li.v1300.add(li.v1340).add(li.v1350);
		number = new Number(2, 29, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);			
		//32 = 1360
		tmp = li.v1360.add(li.v3430);
		number = new Number(2, 30, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);	
		//33 = 1200,1250,1280,161000,163000,2100,2150,2180,291000
		tmp = li.v1200.add(li.v1250).add(li.v1280).add(li.v161000).add(li.v163000).add(li.v2100).add(li.v2150).add(li.v2180).add(li.v291000);
		number = new Number(2, 31, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);			
		//34 = 331000,332000,333000,334000,336000,337000,338000,3390,341000,342000,4100,4170,421000,422000
//		tmp = li.v331000.add(li.v332000).add(li.v333000).add(li.v334000).add(li.v336000).add(li.v337000).add(li.v338000).add(li.v3390).add(li.v341000).add(li.v342000).add(li.v4100).add(li.v4170).add(li.v421000).add(li.v422000);
		tmp = li.v331000.add(li.v332000).add(li.v333000).add(li.v334000).add(li.v336000).add(li.v337000).add(li.v338000).add(li.v3390).add(li.v4100).add(li.v4170).add(li.v421000).add(li.v422000);		
		number = new Number(2, 32, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);					
		//35 = 3100,3200
		tmp = li.v3100;

		tmp = (Thousand && tmp.compareTo(zero) != 0) ? (tmp =  tmp.divide(new BigDecimal(1000))) : tmp;
		number = new Number(2, 33, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//36 = 3500,441000,443000
		tmp = li.v3500.add(li.v441000).add(li.v443000).add(li.v3200).add(li.v341000);
		number = new Number(2, 34, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		
		tmp = li.v41;
		tmp = (Thousand && tmp.compareTo(zero) != 0) ? (tmp =  tmp.divide(new BigDecimal(1000))) : tmp;	
		number = new Number(2, 36, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);			

		
		// ----------------- get direct process 
		BigDecimal v040 = getData(tsStartDate, tsEndDate, "041");
		v040 = v040.add(getData(tsStartDate, tsEndDate, "042"));
		v040 = v040.add(getData(tsStartDate, tsEndDate, "043"));
		v040 = v040.add(getData(tsStartDate, tsEndDate, "044"));
		v040 = v040.add(getData(tsStartDate, tsEndDate, "045"));
		v040 = v040.add(getData(tsStartDate, tsEndDate, "046"));
		v040 = v040.add(getData(tsStartDate, tsEndDate, "047"));
		v040 = v040.add(getData(tsStartDate, tsEndDate, "048"));
		v040 = v040.add(getData(tsStartDate, tsEndDate, "049"));
		v040 = v040.add(getData(tsStartDate, tsEndDate, "050"));
		v040 = v040.add(getData(tsStartDate, tsEndDate, "051"));
		
		BigDecimal v060 = new BigDecimal(0);
		v060 = v060.add(getData(tsStartDate, tsEndDate, "061"));
		v060 = v060.add(getData(tsStartDate, tsEndDate, "062"));
		v060 = v060.add(getData(tsStartDate, tsEndDate, "063"));
		v060 = v060.add(getData(tsStartDate, tsEndDate, "064"));
		v060 = v060.add(getData(tsStartDate, tsEndDate, "065"));
		v060 = v060.add(getData(tsStartDate, tsEndDate, "066"));
		v060 = v060.add(getData(tsStartDate, tsEndDate, "067"));
		v060 = v060.add(getData(tsStartDate, tsEndDate, "068"));
		v060 = v060.add(getData(tsStartDate, tsEndDate, "069"));
		v060 = v060.add(getData(tsStartDate, tsEndDate, "070"));
		v060 = v060.add(getData(tsStartDate, tsEndDate, "071"));
		
		BigDecimal v200 = v040.subtract(v060);
		//v200 = (Thousand && v200.compareTo(zero) != 0) ? (v200 =  v200.divide(new BigDecimal(1000))) : v200;	
		number = new Number(2, 39, v200.doubleValue(), valuestyleNoBold);
		sheet.addCell(number);		
		
		BigDecimal v090 = new BigDecimal(0);
		v090 = v090.add(getData(tsStartDate, tsEndDate, "091"));
		v090 = v090.add(getData(tsStartDate, tsEndDate, "092"));
		v090 = v090.add(getData(tsStartDate, tsEndDate, "093"));
		v090 = v090.add(getData(tsStartDate, tsEndDate, "094"));
		
		BigDecimal v100 = new BigDecimal(0);
		v100 = v100.add(getData(tsStartDate, tsEndDate, "101"));
		v100 = v100.add(getData(tsStartDate, tsEndDate, "102"));
		v100 = v100.add(getData(tsStartDate, tsEndDate, "103"));
		v100 = v100.add(getData(tsStartDate, tsEndDate, "104"));
		v100 = v100.add(getData(tsStartDate, tsEndDate, "105"));
		
		BigDecimal v300 = v090.subtract(v100);
		number = new Number(2, 40, v300.doubleValue(), valuestyleNoBold);
		sheet.addCell(number);		
		
		tmp = getDatabyAcct(tsStartDate, tsEndDate, "104%");
		number = new Number(2, 43, tmp.doubleValue(), valuestyleNoBold);
		sheet.addCell(number);				
		
		Date d = new Date(tsEndDate.getTime());
		Calendar c = Calendar.getInstance();
		c.setTime(d);
		c.add(Calendar.DAY_OF_MONTH, 1);
		
		Timestamp ts = new Timestamp(c.getTimeInMillis());
		tmp = getDatabyAcct(ts, ts, "104%");
		number = new Number(2, 44, tmp.doubleValue(), valuestyleNoBold);
		sheet.addCell(number);		
		
		// set formulas -----------------------------------------------------------------------------
		Formula formula = null;
		
		formula = new Formula(2,28,"SUM(C14:C28)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);				

		formula = new Formula(2,35,"SUM(C30:C35)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);		
		
		formula = new Formula(2,38,"C13+C29+C36+C37+C38");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);		
		
		formula = new Formula(2,42,"C39+C40+C41");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);		
		
//		formula = new Formula(2,44,"C43+C44");
//		formula.setCellFormat(valuestyleBold);
//		sheet.addCell(formula);				
		
		//prev datas----------------------------------------------------------------------------------
		l = getDatas(tsStartDateMinusYear, tsEndDateMinusYear);
		if (getSQlresult != null)
			return Msg.translate(m_ctx, "Ошибка запроса.");		
		
		if (l.size() == 0)
			return Msg.translate(m_ctx, "По заданому параметру нет данных");

		li = l.get(0);
		
		
		zero = new BigDecimal(0);
		// output datas ----------------------------------{
//		li.v541000 = (li.v541000 == null) ? 0 : li.v541000;
		li.v541000 = (Thousand && li.v541000.compareTo(zero) != 0) ? (li.v541000 =  li.v541000.divide(new BigDecimal(1000))) : li.v541000;
		li.v771000 = (Thousand && li.v771000.compareTo(zero) != 0) ? (li.v771000 =  li.v771000.divide(new BigDecimal(1000))) : li.v771000;

		li.v232000 = (Thousand && li.v232000.compareTo(zero) != 0) ? (li.v232000 =  li.v232000.divide(new BigDecimal(1000))) : li.v232000;
		li.v242000 = (Thousand && li.v242000.compareTo(zero) != 0) ? (li.v242000 =  li.v242000.divide(new BigDecimal(1000))) : li.v242000;
		li.v262002 = (Thousand && li.v262002.compareTo(zero) != 0) ? (li.v262002 =  li.v262002.divide(new BigDecimal(1000))) : li.v262002;
		li.v274000 = (Thousand && li.v274000.compareTo(zero) != 0) ? (li.v274000 =  li.v274000.divide(new BigDecimal(1000))) : li.v274000;
		li.v272000 = (Thousand && li.v272000.compareTo(zero) != 0) ? (li.v272000 =  li.v272000.divide(new BigDecimal(1000))) : li.v272000;
		
		li.v621000 = (Thousand && li.v621000.compareTo(zero) != 0) ? (li.v621000 =  li.v621000.divide(new BigDecimal(1000))) : li.v621000;
		li.v741000 = (Thousand && li.v741000.compareTo(zero) != 0) ? (li.v741000 =  li.v741000.divide(new BigDecimal(1000))) : li.v741000;
		
		li.v614000 = (Thousand && li.v614000.compareTo(zero) != 0) ? (li.v614000 =  li.v614000.divide(new BigDecimal(1000))) : li.v614000;
		
		li.v615000 = (Thousand && li.v615000.compareTo(zero) != 0) ? (li.v615000 =  li.v615000.divide(new BigDecimal(1000))) : li.v615000;
		
		li.v611000 = (Thousand && li.v611000.compareTo(zero) != 0) ? (li.v611000 =  li.v611000.divide(new BigDecimal(1000))) : li.v611000;
		li.v612000 = (Thousand && li.v612000.compareTo(zero) != 0) ? (li.v612000 =  li.v612000.divide(new BigDecimal(1000))) : li.v612000;
		li.v613000 = (Thousand && li.v613000.compareTo(zero) != 0) ? (li.v613000 =  li.v613000.divide(new BigDecimal(1000))) : li.v613000;		
		li.v616000 = (Thousand && li.v616000.compareTo(zero) != 0) ? (li.v616000 =  li.v616000.divide(new BigDecimal(1000))) : li.v616000;
		li.v7300 = (Thousand && li.v7300.compareTo(zero) != 0) ? (li.v7300 =  li.v7300.divide(new BigDecimal(1000))) : li.v7300;
		
		li.v335000 = (Thousand && li.v335000.compareTo(zero) != 0) ? (li.v335000 =  li.v335000.divide(new BigDecimal(1000))) : li.v335000;	

		li.v625000 = (Thousand && li.v625000.compareTo(zero) != 0) ? (li.v625000 =  li.v625000.divide(new BigDecimal(1000))) : li.v625000;
		li.v743000 = (Thousand && li.v743000.compareTo(zero) != 0) ? (li.v743000 =  li.v743000.divide(new BigDecimal(1000))) : li.v743000;
		
		li.v7600 = (Thousand && li.v7600.compareTo(zero) != 0) ? (li.v7600 =  li.v7600.divide(new BigDecimal(1000))) : li.v7600;
		
		li.v1300 = (Thousand && li.v1300.compareTo(zero) != 0) ? (li.v1300 =  li.v1300.divide(new BigDecimal(1000))) : li.v1300;
		li.v1340 = (Thousand && li.v1340.compareTo(zero) != 0) ? (li.v1340 =  li.v1340.divide(new BigDecimal(1000))) : li.v1340;
		li.v1340 = (Thousand && li.v1340.compareTo(zero) != 0) ? (li.v1340 =  li.v1340.divide(new BigDecimal(1000))) : li.v1340;
		
		li.v1360 = (Thousand && li.v1360.compareTo(zero) != 0) ? (li.v1360 =  li.v1360.divide(new BigDecimal(1000))) : li.v1360;
		li.v3430 = (Thousand && li.v3430.compareTo(zero) != 0) ? (li.v3430 =  li.v3430.divide(new BigDecimal(1000))) : li.v3430;
		
		li.v1200 = (Thousand && li.v1200.compareTo(zero) != 0) ? (li.v1200 =  li.v1200.divide(new BigDecimal(1000))) : li.v1200;
		li.v1250 = (Thousand && li.v1250.compareTo(zero) != 0) ? (li.v1250 =  li.v1250.divide(new BigDecimal(1000))) : li.v1250;
		li.v1280 = (Thousand && li.v1280.compareTo(zero) != 0) ? (li.v1280 =  li.v1280.divide(new BigDecimal(1000))) : li.v1280;
		li.v161000 = (Thousand && li.v161000.compareTo(zero) != 0) ? (li.v161000 =  li.v161000.divide(new BigDecimal(1000))) : li.v161000;
		li.v163000 = (Thousand && li.v163000.compareTo(zero) != 0) ? (li.v163000 =  li.v163000.divide(new BigDecimal(1000))) : li.v163000;
		li.v2100 = (Thousand && li.v2100.compareTo(zero) != 0) ? (li.v2100 =  li.v2100.divide(new BigDecimal(1000))) : li.v2100;
		li.v2150 = (Thousand && li.v2150.compareTo(zero) != 0) ? (li.v2150 =  li.v2150.divide(new BigDecimal(1000))) : li.v2150;
		li.v2180 = (Thousand && li.v2180.compareTo(zero) != 0) ? (li.v2180 =  li.v2180.divide(new BigDecimal(1000))) : li.v2180;
		li.v2180 = (Thousand && li.v2180.compareTo(zero) != 0) ? (li.v2180 =  li.v2180.divide(new BigDecimal(1000))) : li.v2180;
		
		li.v331000 = (Thousand && li.v331000.compareTo(zero) != 0) ? (li.v331000 =  li.v331000.divide(new BigDecimal(1000))) : li.v331000;
		li.v332000 = (Thousand && li.v332000.compareTo(zero) != 0) ? (li.v332000 =  li.v332000.divide(new BigDecimal(1000))) : li.v332000;
		li.v333000 = (Thousand && li.v333000.compareTo(zero) != 0) ? (li.v333000 =  li.v333000.divide(new BigDecimal(1000))) : li.v333000;
		li.v334000 = (Thousand && li.v334000.compareTo(zero) != 0) ? (li.v334000 =  li.v334000.divide(new BigDecimal(1000))) : li.v334000;
		li.v336000 = (Thousand && li.v336000.compareTo(zero) != 0) ? (li.v336000 =  li.v336000.divide(new BigDecimal(1000))) : li.v336000;
		li.v337000 = (Thousand && li.v337000.compareTo(zero) != 0) ? (li.v337000 =  li.v337000.divide(new BigDecimal(1000))) : li.v337000;
		li.v338000 = (Thousand && li.v338000.compareTo(zero) != 0) ? (li.v338000 =  li.v338000.divide(new BigDecimal(1000))) : li.v338000;
		li.v3390 = (Thousand && li.v3390.compareTo(zero) != 0) ? (li.v3390 =  li.v3390.divide(new BigDecimal(1000))) : li.v3390;
		li.v341000 = (Thousand && li.v341000.compareTo(zero) != 0) ? (li.v341000 =  li.v341000.divide(new BigDecimal(1000))) : li.v341000;
		li.v342000 = (Thousand && li.v342000.compareTo(zero) != 0) ? (li.v342000 =  li.v342000.divide(new BigDecimal(1000))) : li.v342000;
		li.v4100 = (Thousand && li.v4100.compareTo(zero) != 0) ? (li.v4100 =  li.v4100.divide(new BigDecimal(1000))) : li.v4100;
		li.v4170 = (Thousand && li.v4170.compareTo(zero) != 0) ? (li.v4170 =  li.v4170.divide(new BigDecimal(1000))) : li.v4170;
		li.v421000 = (Thousand && li.v421000.compareTo(zero) != 0) ? (li.v421000 =  li.v421000.divide(new BigDecimal(1000))) : li.v421000;
		li.v422000 = (Thousand && li.v422000.compareTo(zero) != 0) ? (li.v422000 =  li.v422000.divide(new BigDecimal(1000))) : li.v422000;
		
		li.v422000 = (Thousand && li.v422000.compareTo(zero) != 0) ? (li.v422000 =  li.v422000.divide(new BigDecimal(1000))) : li.v422000;
		li.v3200 = (Thousand && li.v3200.compareTo(zero) != 0) ? (li.v3200 =  li.v3200.divide(new BigDecimal(1000))) : li.v3200;

		li.v3500 = (Thousand && li.v3500.compareTo(zero) != 0) ? (li.v3500 =  li.v3500.divide(new BigDecimal(1000))) : li.v3500;
		li.v441000 = (Thousand && li.v441000.compareTo(zero) != 0) ? (li.v441000 =  li.v441000.divide(new BigDecimal(1000))) : li.v441000;
		li.v443000 = (Thousand && li.v443000.compareTo(zero) != 0) ? (li.v443000 =  li.v443000.divide(new BigDecimal(1000))) : li.v443000;
		
		tmp = null;
		//010 = 541000 минус 771000
		tmp = li.v541000.add(li.v771000);
		number = new Number(3, 12, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//011 = 232000,242000,2620002,274000
		tmp = li.v232000.add(li.v242000).add(li.v262002).add(li.v274000);
		number = new Number(3, 13, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//12 = 272000
		tmp = li.v272000;
		number = new Number(3, 14, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//13 = 744 000
		tmp = li.v744000;
		number = new Number(3, 15, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//15 = 621000 - 741000
		tmp = li.v621000.subtract(li.v741000);
		number = new Number(3, 17, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//16 = 614 000
		tmp = li.v614000;
		number = new Number(3, 18, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//18 = 615 000
		tmp = li.v615000;
		number = new Number(3, 20, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//19 = 611000,612000,613000,615000,616000 минус 7300
		tmp = li.v611000.add(li.v612000).add(li.v613000).add(li.v615000).add(li.v616000).subtract(li.v7300);
		number = new Number(3, 21, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//20 = 335 000
		tmp = li.v335000;
		number = new Number(3, 22, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//23 = 625000 минус 743000
		tmp = li.v625000.subtract(li.v743000);
		number = new Number(3, 25, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);		
		//24 = 6400 минус 7600
		tmp = li.v614000.subtract(li.v7600);
		number = new Number(3, 26, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//27 = 0
		tmp = new BigDecimal(0);
		number = new Number(3, 27, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);				
		//31 = 1300,1340,1350
		tmp = li.v1300.add(li.v1340).add(li.v1350);
		number = new Number(3, 29, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);			
		//32 = 1360 + 3430
		tmp = li.v1360.add(li.v3430);
		number = new Number(3, 30, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);	
		//33 = 1200,1250,1280,161000,163000,2100,2150,2180,291000
		tmp = li.v1200.add(li.v1250).add(li.v1280).add(li.v161000).add(li.v163000).add(li.v2100).add(li.v2150).add(li.v2180).add(li.v291000);
		number = new Number(3, 31, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);			
		//34 = 331000,332000,333000,334000,336000,337000,338000,3390,341000,342000,4100,4170,421000,422000
//		tmp = li.v331000.add(li.v332000).add(li.v333000).add(li.v334000).add(li.v336000).add(li.v337000).add(li.v338000).add(li.v3390).add(li.v341000).add(li.v342000).add(li.v4100).add(li.v4170).add(li.v421000).add(li.v422000);
		tmp = li.v331000.add(li.v332000).add(li.v333000).add(li.v334000).add(li.v336000).add(li.v337000).add(li.v338000).add(li.v3390).add(li.v4100).add(li.v4170).add(li.v421000).add(li.v422000);		
		number = new Number(3, 32, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);					
		//35 = 3100,3200
		tmp = li.v3100;
		tmp = (Thousand && tmp.compareTo(zero) != 0) ? (tmp =  tmp.divide(new BigDecimal(1000))) : tmp;
		number = new Number(3, 33, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);
		//36 = 3500,441000,443000
		tmp = li.v3500.add(li.v441000).add(li.v443000).add(li.v3200).add(li.v341000);
		number = new Number(3, 34, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);			
		
		tmp = li.v41;
		tmp = (Thousand && tmp.compareTo(zero) != 0) ? (tmp =  tmp.divide(new BigDecimal(1000))) : tmp;	
		number = new Number(3, 36, tmp.doubleValue(), valuestyle);
		sheet.addCell(number);			
		
		
		
		// ----------------- get direct process 
		v040 = getData(tsStartDateMinusYear, tsEndDateMinusYear, "041");
		v040 = v040.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "042"));
		v040 = v040.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "043"));
		v040 = v040.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "044"));
		v040 = v040.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "045"));
		v040 = v040.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "046"));
		v040 = v040.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "047"));
		v040 = v040.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "048"));
		v040 = v040.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "049"));
		v040 = v040.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "050"));
		v040 = v040.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "051"));
		
		v060 = new BigDecimal(0);
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "061"));
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "062"));
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "063"));
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "064"));
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "065"));
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "066"));
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "067"));
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "068"));
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "069"));
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "070"));
		v060 = v060.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "071"));
		
		v200 = v040.subtract(v060);
		number = new Number(3, 39, v200.doubleValue(), valuestyleNoBold);
		sheet.addCell(number);				
		
		v090 = new BigDecimal(0);
		v090 = v090.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "091"));
		v090 = v090.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "092"));
		v090 = v090.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "093"));
		v090 = v090.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "094"));
		
		v100 = new BigDecimal(0);
		v100 = v100.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "101"));
		v100 = v100.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "102"));
		v100 = v100.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "103"));
		v100 = v100.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "104"));
		v100 = v100.add(getData(tsStartDateMinusYear, tsEndDateMinusYear, "105"));
		
		number = new Number(3, 40, v100.doubleValue(), valuestyleNoBold);
		sheet.addCell(number);
		
		tmp = getDatabyAcct(tsStartDateMinusYear, tsEndDateMinusYear, "104%");
		number = new Number(3, 43, tmp.doubleValue(), valuestyleNoBold);
		sheet.addCell(number);
		
		Date d2 = new Date(tsEndDateMinusYear.getTime());
		Calendar c2 = Calendar.getInstance();
		c2.setTime(d2);
		c2.add(Calendar.DAY_OF_MONTH, 1);
		
		Timestamp ts2 = new Timestamp(c2.getTimeInMillis());		
		
		tmp = getDatabyAcct(ts2, ts2, "104%");
		number = new Number(3, 44, tmp.doubleValue(), valuestyleNoBold);
		sheet.addCell(number);
		// set formulas -----------------------------------------------------------------------------
		formula = null;
		
		formula = new Formula(3,28,"SUM(D14:D28)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);				

		formula = new Formula(3,35,"SUM(D30:D35)");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);		
		
		formula = new Formula(3,38,"D13+D29+D36+D37+D38");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);		
		
		formula = new Formula(3,42,"D39+D40+D41");
		formula.setCellFormat(valuestyleBold);
		sheet.addCell(formula);		
		
//		formula = new Formula(3,44,"D43+D44");
//		formula.setCellFormat(valuestyleBold);
//		sheet.addCell(formula);						
		
		// output datas -----------------------------------------}
		copy.write();
		copy.close();
		tableBook.close();
		temporaryFile.delete();

		try {
			String fileName = Util.localFilePath + "FI_Form3.xls";
			String command = "excel \"" + fileName + "\"";
			Runtime.getRuntime().exec("cmd /c start " + command);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return Msg.translate(m_ctx, "Success");
	}

	private List<line> getDatas(Timestamp d1, Timestamp d2) {
		List<line> list = new ArrayList<line>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		StringBuilder sql = getSQl(d1, d2);

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				line l = new line();
				l.v541000 = rs.getBigDecimal(1);	l.v771000 = rs.getBigDecimal(2);
				
				l.v232000 = rs.getBigDecimal(3);	l.v242000 = rs.getBigDecimal(4);	l.v262002 = rs.getBigDecimal(5);	l.v274000 = rs.getBigDecimal(6);

				l.v272000 = rs.getBigDecimal(7);
				l.v621000 = rs.getBigDecimal(8);	l.v741000 = rs.getBigDecimal(9);
				
				l.v614000 = rs.getBigDecimal(10);
				l.v615000 = rs.getBigDecimal(11);
				l.v611000 = rs.getBigDecimal(12);	l.v612000  = rs.getBigDecimal(13);	l.v613000 = rs.getBigDecimal(14);	l.v616000 = rs.getBigDecimal(15);	l.v7300 = rs.getBigDecimal(16);

				l.v335000 = rs.getBigDecimal(17);
				l.v625000 = rs.getBigDecimal(18);	l.v743000 = rs.getBigDecimal(19);
				
				l.v7600 = rs.getBigDecimal(20);
				l.v1300 = rs.getBigDecimal(21);	l.v1340 = rs.getBigDecimal(22);	l.v1350 = rs.getBigDecimal(23);
				
				l.v1360 = rs.getBigDecimal(24); 	
				l.v1200 = rs.getBigDecimal(25);
				l.v1250 = rs.getBigDecimal(26);	l.v1280 = rs.getBigDecimal(27);	l.v161000 = rs.getBigDecimal(28);	l.v163000 = rs.getBigDecimal(29);
				l.v2100 = rs.getBigDecimal(30);	l.v2150 = rs.getBigDecimal(31);	l.v2180 = rs.getBigDecimal(32);	l.v291000 = rs.getBigDecimal(33);
								
				l.v331000 = rs.getBigDecimal(34);	l.v332000 = rs.getBigDecimal(35);	l.v333000 = rs.getBigDecimal(36);	l.v334000 = rs.getBigDecimal(37);
				l.v336000 = rs.getBigDecimal(38);	l.v337000 = rs.getBigDecimal(39);	l.v338000 = rs.getBigDecimal(40);	l.v3390 = rs.getBigDecimal(41);
				l.v341000 = rs.getBigDecimal(42);	l.v342000 = rs.getBigDecimal(43);	l.v4100 = rs.getBigDecimal(44);		l.v4170 = rs.getBigDecimal(45);
				l.v421000 = rs.getBigDecimal(46);	l.v422000 = rs.getBigDecimal(47);
				
				l.v3100 = rs.getBigDecimal(48);		l.v3200 = rs.getBigDecimal(49);
				
				l.v3500 = rs.getBigDecimal(50);
				l.v441000 = rs.getBigDecimal(51);				
				l.v443000 = rs.getBigDecimal(52);
				l.v744000 = rs.getBigDecimal(53);
				l.v3430 = rs.getBigDecimal(54);
				l.v41 = rs.getBigDecimal(55);	
				
				list.add(l);
			}
		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_Form3", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return list;
	}



	private StringBuilder getSQl(Timestamp PstartDate, Timestamp PendDate) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				  "SELECT "
				+ "v541000  	,v771000 "
				+ ",v232000  ,v242000	,v262000  ,v274000 "
				+ ",v272000 "
				+ ",v621000 	,v741000 "
				+ ",v614000 "//10
				+ ",v615000 "
				+ ",v611000	,v612000 	,v613000	,v616000	,v7300 "
				+ ",v335000	 "
				+ ",v625000 	,v743000 "
				+ ",v7600 "//20
				+ ",v1300 		,v1340	,v1350 "
				+ ",v1360 		"
				+ ",v1200	,v1250	,v1280	,v161000	,v163000	,v2100	,v2150	,v2180	,v291000 "//33
				+ ",v331000	,v332000	,v333000	,v334000	,v336000	,v337000	,v338000	,v3390	,v341000	,v342000	,v4100	,v4170	,v421000	,v422000 "//47
				+ ",v3100	,v3200 "
				+ ",v3500	,v441000	,v443000 "
				+ ",v744000 , v3430	, v41 "
				+ " from( "
						+ "select  "

				+ "( select case when (sum(fa.amtacctcr - fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr - fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '541000%') v541000 "
				+ ",(select case when (sum(fa.amtacctdr)) is null then 0 else (sum(fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '771000%') v771000 "

				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '232000%') v232000 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '242000%') v242000 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '262000%') v262000 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '274000%') v274000 "

				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '272000%') v272000 "

				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '744000%') v744000 "

				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '621000%') v621000 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '741000%') v741000 "

				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '614000%') v614000 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '615000%') v615000 "

				+ ",(select case when (sum(fa.amtacctcr)) is null then 0 else (sum(fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '611000%') v611000 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '612000%') v612000 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '613000%') v613000 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '616000%') v616000 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '7300%') v7300 "

				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '335000%') v335000 "

				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '625000%') v625000 "
				+ ",(select case when (sum(fa.amtacctdr)) is null then 0 else (sum(fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '743000%') v743000 "

				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '6400%') v6400 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '7600%') v7600 "

				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '1300%') v1300 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '1340%') v1340 "
				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '1350%') v1350 "

				+ ",(select case when (sum(fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr + fa.amtacctdr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '1360%') v1360 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '3430%') v3430 "				

				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and (ce.value like '1210%' or ce.value like '1220%' or ce.value like '1270%' or ce.value like '1420%')) v1200 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '1250%') v1250 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '1280%') v1280 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and (ce.value like '161000%' or ce.value like '162000%')) v161000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '163000%') v163000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '2100%') v2100 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '2150%') v2150 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '2180%') v2180 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '291000%') v291000 "

				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '331000%') v331000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '332000%') v332000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '333000%') v333000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '334000%') v334000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '336000%') v336000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '337000%') v337000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '338000%') v338000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '3390%') v3390 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '341000%') v341000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '342000%') v342000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '4100%') v4100 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '4170%') v4170 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '421000%') v421000 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '422000%') v422000 "

				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '31%') v3100 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '32%') v3200 "

				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '3500%') v3500 "
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '441000%') v441000 "	
				+ ",(select case when (sum(-fa.amtacctdr + fa.amtacctcr)) is null then 0 else (sum(-fa.amtacctdr + fa.amtacctcr)) end from fact_acct fa, c_elementvalue ce where fa.account_id=ce.c_elementvalue_id	and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"' and ce.value like '443000%') v443000 "       
				
				+ "	,(select 	" 
				+ "		(select case when (sum(fa.amtacctdr )) is null then 0 else (sum(fa.amtacctdr )) end as v 	"
				+ "		from adempiere.fact_acct fa, adempiere.c_elementvalue ce 	"
				+ "		where fa.account_id=ce.c_elementvalue_id	"
				+ "		and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"'	" 
				+ "		and ce.value like '1060%' 	"
				+ "		)	"
				+ "		+	"
				+ "		(	"
				+ "		select case when (sum(- fa.amtacctcr + fa.amtacctdr)) is null then 0 else (sum(- fa.amtacctcr + fa.amtacctdr)) end as v	"
				+ "		from adempiere.fact_acct fa, adempiere.c_elementvalue ce 	"
				+ "		where fa.account_id=ce.c_elementvalue_id		"
				+ "		and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"'	" 
				+ "		and ce.value like '1270%' 	"
				+ "		)	"
				+ "		-	"
				+ "		(	"
				+ "		select case when (sum(fa.amtacctdr)) is null then 0 else (sum(fa.amtacctdr)) end as v	"
				+ "		from adempiere.fact_acct fa, adempiere.c_elementvalue ce 	"
				+ "		where fa.account_id=ce.c_elementvalue_id	"
				+ "		and fa.dateacct between '"+PstartDate+"' and '"+PendDate+"'	" 
				+ "		and ce.value like '7710%' 	"
				+ "			"
				+ "	) )	as v41 "
					
				
				+ ") as main "

				
				);
		
//		System.out.println(sql);
		return sql;
	}


	private BigDecimal getData(Timestamp d1, Timestamp d2, String fi_code) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BigDecimal result = new BigDecimal(0);

		StringBuilder sql = getSQl(d1, d2, fi_code);

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				result = rs.getBigDecimal(1);
			}

			if(result==null) result = new BigDecimal(0);
			result = (Thousand && result.compareTo(new BigDecimal(0)) != 0) ? (result =  result.divide(new BigDecimal(1000))) : result;

		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_Form3Direct", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return result;
	}	
	
	private StringBuilder getSQl(Timestamp PstartDate, Timestamp PendDate, String fi_code) {
		StringBuilder sql = new StringBuilder();
		sql.append(
//				"select "
//				+ " sum(cp.payamt), f3.fi_code " 
//				+ " from c_payment cp "
//				+ " inner join fi_form3 f3 on f3.fi_form3_id = cp.fi_form3_id "
//				+ " where dateacct BETWEEN '" + PstartDate + "' and '"+ PendDate +"' "
//				+ " and fi_code = '" + fi_code + "' "
//				+ " group by f3.fi_code "
				" select(	"
				+ " 						(SELECT case when sum(l.stmtAmt)  is null then 0 else sum(l.stmtAmt)  end	"
				+ " 							FROM adempiere.c_bankstatement h	"
				+ " 							inner join adempiere.c_bankstatementline l on l.c_bankstatement_id = h.c_bankstatement_id	"
				+ " 							inner join fi_form3 f3 on f3.fi_form3_id = l.fi_form3_id	"
				+ " 							where h.statementdate between '" + PstartDate + "' and '"+ PendDate +"' 	"
				+ " 							and f3.fi_code = '" + fi_code + "'	" 
				+ " 							and f3.fi_form3_id not in (select fi_form3_id from adempiere.fi_form3exclude where isactive = 'Y' )  "
				+ "								and h.posted = 'Y'	\n"				
				+ " 						)	"
				+ " 						+ 	"
				+ " 						(select	" 
				+ " 							case when sum(cp.payamt) is null then 0 else sum(cp.payamt) end		"
				+ " 							from c_payment cp 		"
				+ " 							inner join fi_form3 f3 on f3.fi_form3_id = cp.fi_form3_id	"	 
				+ " 							where dateacct between '" + PstartDate + "' and '"+ PendDate +"' 		"
				+ " 							and f3.fi_code = '" + fi_code + "'	"
				+ " 							and f3.fi_form3_id not in (select fi_form3_id from adempiere.fi_form3exclude where isactive = 'Y' )  "
				+ "								and cp.posted = 'Y'	\n"				
				+ " 						)	"
				+ " 						+	"
				+ " 						(	"
				+ " 						select case when sum(l.amtsourcedr) is null then 0 else sum(l.amtsourcedr) end	"
				+ " 							from adempiere.gl_journal h	"
				+ " 							inner join adempiere.gl_journalline l on l.gl_journal_id = h.gl_journal_id	"
				+ " 							inner join fi_form3 f3 on f3.fi_form3_id = l.fi_form3_id 	"
				+ " 							where h.dateacct between '" + PstartDate + "' and '"+ PendDate +"'	"
				+ " 							and f3.fi_code = '" + fi_code + "'	"
				+ " 							and f3.fi_form3_id not in (select fi_form3_id from adempiere.fi_form3exclude where isactive = 'Y' )  "
				+ "								and h.posted = 'Y'	\n"				
				+ " 						)	"
				+ " 					) amt	"
				);
		return sql;
	}
	
	private BigDecimal getDatabyAcct(Timestamp d,Timestamp d2,  String acct) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BigDecimal result = new BigDecimal(0);

		StringBuilder sql = getSQlByAcct(d,d2, acct);

		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				result = rs.getBigDecimal(1);
			}

			if(result==null) result = new BigDecimal(0);
			result = (Thousand && result.compareTo(new BigDecimal(0)) != 0) ? (result =  result.divide(new BigDecimal(1000))) : result;

		} catch (SQLException e) {
			s_log.log(Level.SEVERE, "Report: FI_Form3Direct", e);
			getSQlresult = e.getMessage();
		} finally {
			DB.close(rs, pstmt);
			rs = null;
			pstmt = null;
		}
		return result;
	}
	
	private StringBuilder getSQlByAcct(Timestamp PstartDate, Timestamp PendDate, String acct) {
		StringBuilder sql = new StringBuilder();
		sql.append(
				  " select coalesce(sum(fa.amtacctdr - fa.amtacctcr),0) from fact_acct fa, c_elementvalue ce " 
				+ " where fa.account_id=ce.c_elementvalue_id	"
				+ " and PostingType='A' "		  
				+ "and fa.dateacct < '"+PstartDate+"' and ce.value like '"+acct+"'"
				);
		
//		Calendar cal = Calendar.getInstance();
//		cal.setTime(PstartDate);
//		cal.add(Calendar.MONTH, 1);
//		Timestamp tsStartDatePlusMonth = new Timestamp(cal.getTime().getTime());
//
//		sql.append(
//		"select(	\n" +
//		"		select case when (sum(fa.amtacctdr - fa.amtacctcr)) is null then 0 else (sum(fa.amtacctdr - fa.amtacctcr)) end	\n" + 
//		"		from fact_acct fa, c_elementvalue ce  \n"	+
//		"		where fa.account_id=ce.c_elementvalue_id	\n"	+	 
//		"		and fa.dateacct <= '" + tsStartDatePlusMonth + "' and ce.value like '104%')	\n"	+
//
//		"		+	\n"	+
//		"		(select case when (sum(fa.amtacctcr - fa.amtacctdr)) is null then 0 else (sum(fa.amtacctcr - fa.amtacctdr)) end	\n"	+ 
//		"		from fact_acct fa, c_elementvalue ce	\n"	+  
//		"		where fa.account_id=ce.c_elementvalue_id\n"	+	 
//		"		and fa.dateacct between '"+	PstartDate	+"' and '"+ PendDate +"' and ce.value like '104%'	\n"	+
//		"		)");		
		
		return sql;
	}	
	
}